package com.nekonomics
package persistence.repository

import com.nekonomics.domain.entity.Spu
import com.nekonomics.utils.SqlUtil
import com.nekonomics.utils.magic.{SqlMagic, TypeMagic}
import doobie.*
import doobie.hikari.HikariTransactor
import doobie.implicits.*
import doobie.postgres.implicits.*
import zio.*
import zio.interop.catz.*
import com.nekonomics.utils.SyntaxUtil.*

trait SpuRepo {
  def findById(skuId: Long): ConnectionIO[Option[Spu]]
  def listById(spuId: Long): ConnectionIO[List[Long]]
}

object SpuRepo {
  val live: ULayer[SpuRepo] = ZLayer.succeed(new SpuRepoLive)
  
}

class SpuRepoLive extends SpuRepo {

  override def listById(spuId: Long): ConnectionIO[List[Long]] =
    sql"""
         select sku.id
         from public.spu
         join public.sku on sku.spu_id = spu.id and sku.spu_id = $spuId
       """
      .query[Long]
      .to[List]

  override def findById(skuId: Long): ConnectionIO[Option[Spu]] = {
    val fields = SqlMagic.fieldSnakeNames[Spu] |> (SqlUtil.fieldsWithAlias(_: List[String], Some("spu")))
    (
      fr"select" ++ Fragment.const(fields) ++
      fr"""from public.spu as spu
      join public.sku as sku on spu.id = sku.spu_id
      where sku.id = $skuId
      """
    ).query[Spu]
      .option
  }
}
